package weaver.interfaces.cese.duss.ceseutil;

import weaver.conn.RecordSet;
import weaver.general.Util;

/**
 * @Title: ecology-9-demo
 * @Company: 泛微软件
 * @author: weaver.Dusongsong
 * @version: 1.0
 * @CreateDate: 2020-12-10
 * @Description: ${description}
 * @ModifyLog:
 **/

public class ReserveRecalCulateUtil {
    private static String formmode = "uf_byjglr";

    //申请备用金计算规则
    public static double calReserveApplicat(String fylx, String param) {
        RecordSet rs = new RecordSet();
        double yfsje = 0.00;
        if ("0".equals( fylx )) {//费用类型为个人
            String sql = "select nvl(sum(nvl(sqje,0)),0)sqjes,nvl(sum(nvl(grbyj,0)),0)grbyjs from uf_byjjkjl where fylx=? and jkr=?";
            rs.executeQuery( sql, new Object[]{fylx, param} );
            rs.writeLog( sql, String.join( ",", fylx, param ) );
            if (rs.next()) {
                String sqjes = Util.null2String( rs.getString( "sqjes" ) );//申请金额合计
                String grbyjs = Util.null2String( rs.getString( "grbyjs" ) );//个人备用金合计
                yfsje = Double.valueOf( sqjes ) + Double.valueOf( grbyjs );
            }
        } else {
            String sql = "select nvl(sum(nvl(sqje,0)),0)sqjes,nvl(sum(nvl(xmbyj,0)),0)xmbyjs from uf_byjjkjl where fylx=? and xmbm=?";
            rs.executeQuery( sql, new Object[]{fylx, param} );
            rs.writeLog( sql, String.join( ",", fylx, param ) );
            if (rs.next()) {
                String sqjes = Util.null2String( rs.getString( "sqjes" ) );//申请金额合计
                String xmbyjs = Util.null2String( rs.getString( "xmbyjs" ) );//项目备用金合计
                yfsje = Double.valueOf( sqjes ) + Double.valueOf( xmbyjs );
            }
        }
        return yfsje;
    }

    //费用报销冲抵
    public static double calCostOffsetApplicat(String fylx, String xmbm, String fyr) {
        RecordSet rs = new RecordSet();
        double bccdbyjje = 0.00;
        String bdxmbm = getBdxmbm( xmbm );
        if ("1".equals( fylx )) {//费用类型为项目
            String sql = "select nvl(sum(nvl(bccdbyjje,0)),0) bccdbyjje from uf_fybxxx where gcxmmc is not null and gcxm=?";
            rs.executeQuery( sql, bdxmbm );
            rs.writeLog( sql, String.join( ",", fylx, bdxmbm ) );
            if (rs.next()) {
                bccdbyjje = Double.valueOf( Util.null2String( rs.getString( 1 ) ) );//报销冲抵
            }
        } else {
            String sql = "select nvl(sum(nvl(bccdbyjje,0)),0) bccdbyjje from uf_fybxxx where gcxmmc is null and fyfsr=?";
            rs.executeQuery( sql, fyr );
            rs.writeLog( sql, String.join( ",", fylx, fyr ) );
            if (rs.next()) {
                bccdbyjje = Double.valueOf( Util.null2String( rs.getString( "bccdbyjje" ) ) );//报销冲抵
            }
        }
        return bccdbyjje;
    }

    //个税报销冲抵
    public static double calTaxesApplicat(String sqr) {
        RecordSet rs = new RecordSet();
        double bccdje = 0.00;
        String sql = "select nvl(sum(nvl(bccdje,0)),0) bccdje from uf_gsbx where sqr=?";
        rs.executeQuery( sql, new Object[]{sqr} );
        rs.writeLog( sql, String.join( ",", sqr ) );
        if (rs.next()) {
            bccdje =Double.valueOf( Util.null2String( rs.getString( "bccdje" )));//报销冲抵
        }
        return bccdje;
    }

    //差旅报销冲抵
    public static double calTravelApplicat(String fylx, String xmbm, String fyr) {
        RecordSet rs = new RecordSet();
        String bdxmbm = getBdxmbm( xmbm );
        double bccdbyjje = 0.00;
        if ("1".equals( fylx )) {//费用类型为项目
            String sql = "select nvl(sum(nvl(bccdbyjje,0)),0) bccdbyjje from uf_clfybx where gcxmmc is not null and gcxm=?";
            rs.executeQuery( sql, bdxmbm );
            rs.writeLog( sql, String.join( ",", fylx, bdxmbm ) );
            if (rs.next()) {
                bccdbyjje =Double.valueOf(Util.null2String( rs.getString( "bccdbyjje" )));//报销冲抵
            }
        } else {
            String sql = "select nvl(sum(nvl(bccdbyjje,0)),0) bccdbyjje from uf_clfybx where gcxmmc is null and fycdr=?";
            rs.executeQuery( sql,  fyr );
            rs.writeLog( sql, String.join( ",", fylx, fyr ) );
            if (rs.next()) {
                bccdbyjje =Double.valueOf(Util.null2String( rs.getString( "bccdbyjje" )));//报销冲抵
            }
        }
        return bccdbyjje;
    }

    //备用金借款金额=备用金初始+备用金借款金额+(转移金额(转入)-转移金额(转出))-还款-冲抵
    public static double calReserveBorrow(String fylx, String xmbm, String byjzrr) {
        RecordSet rs = new RecordSet();
        RecordSet rs1 = new RecordSet();
        RecordSet rs2 = new RecordSet();
        double yfsjes = 0.00;//备用金借款金额
        double byjcs = 0.00;//备用金初始
        double spje = 0.00;//备用金借款金额
        double ghje = 0.00;//ghje
        String sql = "";
        String sql1 = "";
        String sql2 = "";
        if ("1".equals( fylx )) {//费用类型为项目
            sql = "select nvl(sum(nvl(byjcs,0)),0)byjcs from uf_byjglr where byjlx='"+fylx+"' and xmbm='"+xmbm+"'";
            rs.executeQuery( sql);
            rs.writeLog( sql);
            if(rs.next()) {
                byjcs = Util.getDoubleValue( rs.getString( "byjcs" )) ;//备用金初始
            }
            sql1 = "select nvl(sum(nvl(ghje,0)),0) from uf_byjhkjl where byjlx='"+fylx+"' and xmbm='"+xmbm+"'";
            rs1.executeQuery( sql1 );
            rs1.writeLog( sql1);
            if(rs1.next()){
                ghje = Util.getDoubleValue( rs1.getString(1));//归还金额
            }
            sql2 = "select nvl(sum(nvl(spje,0)),0) from uf_byjjkjl where fylx='"+fylx+"' and xmbm='"+xmbm+"'";
            rs2.executeQuery( sql2 );
            rs2.writeLog( sql2);
            if(rs2.next()){
                spje = Util.getDoubleValue( rs2.getString(1));//备用金借款金额
            }
            double bccdbyjje = ReserveRecalCulateUtil.calCostOffsetApplicat( fylx, xmbm, byjzrr );//费用报销冲抵
            double bccdbyjje1 = ReserveRecalCulateUtil.calTravelApplicat( fylx, xmbm, byjzrr );//差旅报销冲抵
            double zcbyj = ReserveRecalCulateUtil.addTravelTrans( fylx, xmbm, byjzrr );
            double zrbyj = ReserveRecalCulateUtil.minusTravelTrans( fylx, xmbm, byjzrr );
            yfsjes = byjcs  + spje  + (zrbyj - zcbyj) - ghje - (bccdbyjje + bccdbyjje1);
        } else {
            sql = "select nvl(sum(nvl(byjcs,0)),0)byjcs,nvl(sum(nvl(yfsje,0)),0)yfsje from uf_byjglr where byjlx='"+fylx+"' and byjzrr='"+byjzrr+"'";
            rs.executeQuery( sql );
            rs.writeLog( sql);
            if(rs.next()){
                byjcs = Util.getDoubleValue( rs.getString( "byjcs" )) ;//备用金初始
            }
            sql1 = "select nvl(sum(nvl(ghje,0)),0) from uf_byjhkjl where byjlx='"+fylx+"' and byjzrr='"+byjzrr+"'";
            rs1.executeQuery( sql1);
            rs1.writeLog( sql1);
            if(rs1.next()){
                ghje = Util.getDoubleValue( rs1.getString(1));//归还金额
            }
            sql2 = "select nvl(sum(nvl(spje,0)),0) from uf_byjjkjl where fylx='"+fylx+"' and jkr='"+byjzrr+"'";
            rs2.executeQuery( sql2 );
            rs2.writeLog( sql2);
            if(rs2.next()){
                spje = Util.getDoubleValue( rs2.getString(1));//备用金借款金额
            }
            double bccdje = ReserveRecalCulateUtil.calTaxesApplicat( byjzrr );//个税报销冲抵
            double bccdbyjje = ReserveRecalCulateUtil.calCostOffsetApplicat( fylx, xmbm, byjzrr );//费用报销冲抵
            double bccdbyjje1 = ReserveRecalCulateUtil.calTravelApplicat( fylx, xmbm, byjzrr );//差旅报销冲抵
            double zcbyj = ReserveRecalCulateUtil.addTravelTrans( fylx, xmbm, byjzrr );
            double zrbyj = ReserveRecalCulateUtil.minusTravelTrans( fylx, xmbm, byjzrr );
            yfsjes =byjcs +  spje  + (zrbyj - zcbyj) - ghje - (bccdbyjje + bccdbyjje1 + bccdje);
        }
        return yfsjes;
    }

    /* 转出（减去）
     项目：字段 nvl(sum（项目备用金）备用金责任人+项目编码+备用金类型（条件）表（转移记录表）
     个人：字段 nvl(sum（个人备用金）备用金责任人+备用金类型（条件）表（转移记录表）*/
    public static double addTravelTrans(String fylx, String xmbm, String byjzrr) {
        RecordSet rs = new RecordSet();
        double zcbyj = 0.00;
        if ("1".equals( fylx )) {//费用类型为项目
            String sql = "select nvl(sum(nvl(xmbyj,0)),0) from  uf_byjjkrbgjl where byjlx='"+fylx+"' and xmbm='"+xmbm+"' and byjzrr='"+byjzrr+"'";
            rs.executeQuery( sql);
            rs.writeLog( sql);
            if(rs.next()){
                zcbyj = Util.getDoubleValue( rs.getString( 1 ));
            }
        } else {
            String sql = "select nvl(sum(nvl(grbyj,0)),0) from  uf_byjjkrbgjl where byjlx='"+fylx+"' and byjzrr='"+byjzrr+"'";
            rs.executeQuery( sql);
            rs.writeLog( sql);
            if(rs.next()){
                zcbyj =Util.getDoubleValue( rs.getString( 1 ));
            }
        }
        return zcbyj;
    }

    /* 转入：加上
    项目：字段 nvl(sum（项目备用金）备用金调整人+项目编码+备用金类型（条件）表（转移记录表）
    个人：字段 nvl(sum（个人备用金）备用金项目：字段 nvl(sum（项目备用金）备用金调整人+备用金类型（条件）表（转移记录表）*/
    public static double minusTravelTrans(String fylx, String xmbm, String dzbyjglr) {
        RecordSet rs = new RecordSet();
        double zrbyj = 0.00;
        if ("1".equals( fylx )) {//费用类型为项目
            String sql = "select nvl(sum(nvl(xmbyj,0)),0) from  uf_byjjkrbgjl where byjlx='"+fylx+"' and xmbm='"+xmbm+"' and dzbyjglr='"+dzbyjglr+"'";
            rs.executeQuery( sql);
            rs.writeLog( sql);
            if(rs.next()){
                zrbyj =Util.getDoubleValue( rs.getString( 1 ));
            }

        } else {
            String sql = "select nvl(sum(nvl(grbyj,0)),0) from  uf_byjjkrbgjl where byjlx='"+fylx+"' and dzbyjglr='"+dzbyjglr+"'";
            rs.executeQuery( sql);
            rs.writeLog( sql);
            if(rs.next()){
                zrbyj =Util.getDoubleValue( rs.getString( 1 ));
            }
        }
        return zrbyj;
    }

    //获取建模Id
    public static String getFormmodeid() {
        RecordSet rs = new RecordSet();
        String formmodeid = "";
        rs.executeQuery( "select m.id from modeinfo m left join WORKFLOW_BILL b on m.formid = b.id where b.tablename = ?", formmode );
        if (rs.next()) {
            formmodeid = Util.null2String( rs.getString( "id" ) );
        }
        return formmodeid;
    }

    //判断是否存在备用金台账记录
    public static boolean isExistByj(String byjlx, String lxvalue) {
        RecordSet rs = new RecordSet();
        String sql = "";
        boolean flag = false;
        if ("0".equals( byjlx )) {
            sql = "select * from uf_byjglr where byjlx=? and byjzrr=?";
        }
        if ("1".equals( byjlx )) {
            sql = "select * from uf_byjglr where byjlx=? and xmbm=?";
        }
        rs.executeQuery( sql, new Object[]{byjlx, lxvalue} );
        rs.writeLog( sql, String.join( ",", byjlx, lxvalue ) );
        if (rs.next()) {
            flag = true;
        }
        return flag;
    }
    public static String getBdxmbm(String xmbm) {
        RecordSet rs = new RecordSet();
        String bdxmbm = "";
        rs.executeQuery( "select project_code from uf_crm_eng_con_info where id=?", xmbm );
        rs.next();
        String project_code = Util.null2String( rs.getString( "project_code" ) );
        rs.executeQuery( "select bdxmbm from uf_ImpProjectsSync where ssxmbm=?", project_code );
        boolean flag = rs.next();
        if (flag) {
            bdxmbm = Util.null2String( rs.getString( "bdxmbm" ) );
        } else {
            rs.executeQuery( "select id from uf_crm_bidding_lib where bid_code=?", project_code );
            rs.next();
            bdxmbm = Util.null2String( rs.getString( "id" ) );
        }
        return bdxmbm;
    }
}
